package com.hxqc.basic.dependency.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class ReadExcel {

	/**
	 * 
	 * @Description：
	 * @param is 文件流
	 * @param rowStart 从第几行开始读取（不包含）
	 * @param colStart 从第几列开始读取（不包含）
	 * @return List<Map<String,Object>>
	 */
	public List<Map<String, Object>> readXls(InputStream is, int rowStart, int colStart) throws IOException, EncryptedDocumentException, InvalidFormatException {
		Workbook hssfWorkbook = WorkbookFactory.create(is);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			List<List<Map<String, String>>> headers = new ArrayList<>();
			//read the sheet header
			for(int i = 0; i < rowStart; i++){
				List<Map<String, String>> headerList = new ArrayList<>();
				Row headerRow = hssfSheet.getRow(i);
				for(int j = colStart; j < headerRow.getLastCellNum(); j++){
					Map<String, String> map = new HashMap<>();
					map.put("cellVal", getValue(headerRow.getCell(j)) == null ? "" : getValue(headerRow.getCell(j)).toString());
					Map<String, String> mergedMap = isMergedRegion(hssfSheet, i, j);
					if(mergedMap.get("isMerged").equals("yes")){
						map.put("group", mergedMap.get("index"));
					}
					headerList.add(map);
				}
				if(i == rowStart - 1){
					headers.add(headerList);
				}else{
					headers.add(dealList(headerList));
				}
			}
			//拼接表头
			List<String> keys = joinHeader(headers);
			System.out.println(keys);
			for (int rowNum = rowStart; rowNum < hssfSheet.getLastRowNum(); rowNum++) {
				Row hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					Map<String, Object> map = new HashMap<String, Object>();
					for (int i = colStart, j = hssfRow.getLastCellNum(); i < j; i++) {
						map.put(keys.get(i - colStart), getValue(hssfRow.getCell(i)));
					}
					list.add(map);
				}
			}
		}
		hssfWorkbook.close();
		return list;
	}
	//判断是否是合并单元格，并返回sheet中该合并单元格的索引
	private Map<String, String> isMergedRegion(Sheet sheet,int row ,int column) {   
		Map<String, String> map = new HashMap<>();
	       int sheetMergeCount = sheet.getNumMergedRegions();   
	       for (int i = 0; i < sheetMergeCount; i++) {   
	             CellRangeAddress range = sheet.getMergedRegion(i);   
	             int firstColumn = range.getFirstColumn(); 
	             int lastColumn = range.getLastColumn();   
	             int firstRow = range.getFirstRow();   
	             int lastRow = range.getLastRow();   
	             if(row >= firstRow && row <= lastRow){ 
	                     if(column >= firstColumn && column <= lastColumn){ 
	                             map.put("isMerged", "yes");   
	                             map.put("index", i+""); 
	                             return map;
	                         } 
	             }   
	       } 
	       map.put("isMerged", "no");  
	       return map;   
	     } 
	
	/**
	 * @Description：
	 * @param 
	 * @return List<String>
	 */
	private List<Map<String,String>> dealList(List<Map<String, String>> headerList) {
		if(headerList != null && !headerList.isEmpty()){
			for(int i = 1; i < headerList.size(); i++){
				Map<String, String> map = headerList.get(i);
				Map<String, String> map1 = headerList.get(i-1);
				if(StringUtil.isEmpty(map.get("cellVal")) && map1.containsKey("group") && map.containsKey("group") && map.get("group").equals(map1.get("group"))){
					headerList.set(i, headerList.get(i-1));
				}
			}
			return headerList;
		}
		return null;
	}

	private  List<String> joinHeader(final List<List<Map<String, String>>> headers) {
	    final List<Iterator<Map<String, String>>> it = new LinkedList<>();
	    for (List<Map<String, String>> l : headers) {
	        it.add(l.iterator());
	    }

	    final List<String> combined = new ArrayList<>();
	    int index = 1;
	    boolean flag = false;
	    outer:
	    while (true) {
	        final StringBuilder sb = new StringBuilder();
	        if(flag){
	        	index = 1;
	        }
	        for (final Iterator<Map<String, String>> i : it) {
	            if (!i.hasNext()) {
	            	break outer;
	            }
	            Map<String, String> map = i.next();
	            sb.append(map.get("cellVal")+"-");
	        }
	        String str = sb.toString();
        	str = str.replaceAll("-{1,}$", "");
        	if(combined.contains(str)){
        		str = str + "-"+index;
        		index++;
        		flag = false;
        	}else{
        		flag = true;
        	}
	        combined.add(str);
	    }
	    for (final Iterator<Map<String, String>> i : it) {
	    	if (i.hasNext()) {
	            throw new IllegalArgumentException("Lists not the same length.");
	        }
	    }
	    return combined;
	}

	@SuppressWarnings("static-access")
	private Object getValue(Cell hssfCell) {
		Object value = null;
		if(hssfCell !=null){
			 switch (hssfCell.getCellType()) {
	              case Cell.CELL_TYPE_FORMULA:
					try {
						value = hssfCell.getNumericCellValue();
						DecimalFormat df = new DecimalFormat("#.#########");
		                value = df.format(value);
					} catch (IllegalStateException  e) {
						value = hssfCell.getRichStringCellValue();
					}
	                 break;
	             case Cell.CELL_TYPE_NUMERIC:

					 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					 if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
						//将excel日期时间转换为标准时间格式
						  value= sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString();
						 break;
					 }

	                value = hssfCell.getNumericCellValue();
	                DecimalFormat df = new DecimalFormat("#.#########");
	                value = df.format(value);
	                 break;
	             case Cell.CELL_TYPE_STRING:
	                  value = hssfCell.getStringCellValue();
	                 break;
	             }
		}
		return value;
	}
}